package top.abilities.util.utils.Excel;

import org.apache.poi.hssf.util.HSSFColor;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFRow;

import java.io.File;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @Auhtor: ciro
 * @Description
 * @Date: 2018/1/5 17:32
 * @Modified By:
 */
public class ExcelUtil {
    private FormulaEvaluator evaluationator;
    private Workbook wb=null;
    public Map<String,Map<Integer,String>> sheetHeader;
    //加载workbook
    public  void loadWorkBook(File file){
        try {
            wb= WorkbookFactory.create(file);
            //单元格值为函数
            evaluationator = wb.getCreationHelper().createFormulaEvaluator();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public  void loadWorkBook(InputStream fis){
        try {
            wb= WorkbookFactory.create(fis);
            evaluationator = wb.getCreationHelper().createFormulaEvaluator();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    private void init(){

    }
    public String[] getSheetNames(){
        int sheetNum=wb.getNumberOfSheets();
        String[] sheetNames=new String[sheetNum];
        for(int i=0;i<sheetNum;i++){
            sheetNames[i]=wb.getSheetName(i);
        }
        return sheetNames;
    }
    public Map<String,Map<Integer,String>> getSheetHeader(){

        return null;
    }
    /**
     * 获取某行行的指定列的值
     * @param row
     * @param num
     * @return
     */
    public  Object getCellValue(Row row, int num) {
        if (row.getCell(num) == null) return "";
        else return getCellValue(row.getCell(num));
    }

    /**
     * 获取单元格的值
     * @param cell
     * @return
     */
    public  Object getCellValue(Cell cell){
        // Alternatively, get the value and format it yourself
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                return cell.getRichStringCellValue().getString();
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue();
                } else {
                    return  cell.getNumericCellValue();
                    //return new DecimalFormat("0").format(cell.getNumericCellValue());
                }
            case Cell.CELL_TYPE_BOOLEAN:
                return  cell.getBooleanCellValue();
            case Cell.CELL_TYPE_FORMULA:
                return evaluationator.evaluate(cell).getStringValue();
            case Cell.CELL_TYPE_BLANK:
                return "";
            default:
                return "";
        }
    }

    public Workbook getWb() {
        return wb;
    }

    public void setWb(Workbook wb) {
        evaluationator = wb.getCreationHelper().createFormulaEvaluator();
        this.wb = wb;
    }
    /**
     * 在原有行插入一列数据
     * @param sheetName sheet名称
     * @param rowIndex 行索引
     * @param cellIndex 列索引
     * @param insertContent 插入内容
     */
    public void setCellValue(String sheetName,int rowIndex,int cellIndex,String insertContent){
        Sheet sheet=wb.getSheet(sheetName);
        Row row=sheet.getRow(rowIndex);
        Cell cell=row.createCell(cellIndex);
        CellStyle cellStyle=wb.createCellStyle();
        Font font=wb.createFont();
        font.setColor(HSSFColor.RED.index);
        cellStyle.setFont(font);
        cell.setCellValue(insertContent);
        cell.setCellStyle(cellStyle);
    }


    /**
     * 一些导入导入的值为数字，到后台会转换为科学计数
     * （防止导入的数据与用户输入的数据不一致）
     * @param row
     * @param num
     * @return
     */
    public  Object getCellValuePreventScientificCounting(Row row, int num) {
        if (row.getCell(num) == null) return "";
        else return getCellValuePreventScientificCounting(row.getCell(num));
    }

    /**
     * 一些导入导入的值为数字，到后台会转换为科学计数
     * （防止导入的数据与用户输入的数据不一致）
     * @param cell
     * @return
     */
    public  Object getCellValuePreventScientificCounting(Cell cell){
        // Alternatively, get the value and format it yourself
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                return cell.getRichStringCellValue().getString();
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue();
                } else {
                    return new DecimalFormat("0").format(cell.getNumericCellValue());
                }
            case Cell.CELL_TYPE_BOOLEAN:
                return  cell.getBooleanCellValue();
            case Cell.CELL_TYPE_FORMULA:
                return evaluationator.evaluate(cell).getStringValue();
            case Cell.CELL_TYPE_BLANK:
                return "";
            default:
                return "";
        }
    }
}
